package edu.ccut.saturn.basecomponent.database.util;

import java.util.List;

import edu.ccut.saturn.basecomponent.database.IDeleteSQLBuilder;
import edu.ccut.saturn.basecomponent.database.ISaturnSQL;
import edu.ccut.saturn.basecomponent.database.ISaturnSQLBuilder;
import edu.ccut.saturn.basecomponent.database.ISelectSQLBuilder;
import edu.ccut.saturn.basecomponent.database.IUpdateSQLBuilder;
import edu.ccut.saturn.basecomponent.database.SaturnSQLBuilderFactory;
import edu.ccut.saturn.basecomponent.database.SaturnSQLHandler;
import edu.ccut.saturn.component.ILogicContext;
import edu.ccut.saturn.component.IParameterContext;
import edu.ccut.saturn.component.SaturnData;

public class SQLExecute {
	
	@SuppressWarnings("unchecked")
	public static List<SaturnData> queryByCondition(String condition,IParameterContext parameterContext) throws Exception{
		
		String fullKey = (String) parameterContext.getParameterValue(0);

		String dbType = SaturnDataUtil.getStandardDBType(fullKey);

		ISaturnSQLBuilder builder = SaturnSQLBuilderFactory
				.getSaturnSQLBuilder(dbType);

		if (builder != null) {

			ISelectSQLBuilder selectSQLBuilder = builder
					.getSelectSQLBuilder(getBuilderType(fullKey));

			//String condition = (String) parameterContext.getParameterValue(1);

			List<FieldAndValueVO> voList = null;

			String[] conditions = condition.split(ConditionUtils.SEPARATE);

			condition = conditions[0];

			if (conditions.length == 3) {

				condition = conditions[1];
				if (SaturnSQLBuilderFactory.ORACLE.equals(dbType)) {
					condition = SaturnSQLUtil.parseOracleCondition(condition,
							fullKey);
				}
				ILogicContext logicContext = parameterContext.getLogicContext();

				String key = conditions[2].trim();

				voList = (List<FieldAndValueVO>) logicContext.getValue(key);

			}
			if (condition == null) {
				condition = "";
			}

			condition = "WHERE 1=1 " + condition;

			ISaturnSQL saturnSql = selectSQLBuilder.build(fullKey, condition);

			saturnSql.setConditionList(voList);

			List<SaturnData> list = SaturnSQLHandler.executeQuery(saturnSql,
					parameterContext.getConnection());
			
			return list;
		}
		return null;
	}
	
	@SuppressWarnings("unchecked")
	public static List<SaturnData> queryByConditionWithPage(String condition,IParameterContext parameterContext) throws Exception{
		String fullKey = (String) parameterContext.getParameterValue(0);
		String dbType = SaturnDataUtil.getStandardDBType(fullKey);

		ISaturnSQLBuilder builder = SaturnSQLBuilderFactory
				.getSaturnSQLBuilder(dbType);

		if (builder != null) {
			
			List<FieldAndValueVO> voList = null;
			String[] conditions = condition.split(ConditionUtils.SEPARATE);
			condition = conditions[0].trim();
			if (conditions.length == 3) {

				condition = conditions[1];
				if (SaturnSQLBuilderFactory.ORACLE.equals(dbType)) {
					condition = SaturnSQLUtil.parseOracleCondition(condition,
							fullKey);
				}
				ILogicContext logicContext = parameterContext.getLogicContext();
				String key = conditions[2].trim();
				voList = (List<FieldAndValueVO>) logicContext.getValue(key);

			}
			if (condition == null) {
				condition = "";
			}
			condition = "WHERE 1=1 " + condition;

			/*ISaturnSQL saturnSql = selectSQLBuilder.build(fullKey, condition);

			saturnSql.setConditionList(voList);

			List<SaturnData> list = SaturnSQLHandler.executeQuery(saturnSql,
					parameterContext.getConnection());*/
			
			Object startIndex = parameterContext.getParameterValue(2);
			Object endIndex = parameterContext.getParameterValue(3);
			String tableWord = (String) parameterContext.getParameterValue(5);
			// 解析tableWord
			if (tableWord != null && !"".equals(tableWord.trim())) {
				tableWord = parseCondition(tableWord, parameterContext);
			}
			ISelectSQLBuilder selectSQLBuilder = builder
					.getSelectSQLBuilder(getBuilderType(fullKey));
			ISaturnSQL saturnSql = null;
			if (startIndex != null && endIndex != null) {
				// 分页 (暂时只有分页逻辑支持分区表)
				saturnSql = selectSQLBuilder.build(fullKey, condition,
						tableWord, (Integer) startIndex, (Integer) endIndex);
			} else {
				// 非分页
				saturnSql = selectSQLBuilder.build(fullKey, condition);
			}
			
			saturnSql.setConditionList(voList);
			
			List<SaturnData> list = SaturnSQLHandler.executeQuery(saturnSql,
					parameterContext.getConnection());
			
			return list;
		}
		return null;
	}
	
	@SuppressWarnings("unchecked")
	public static Integer queryCountByCondition(String condition,IParameterContext parameterContext,String tableWord) throws Exception{
		String fullKey = (String) parameterContext.getParameterValue(0);
		String dbType = SaturnDataUtil.getStandardDBType(fullKey);
		ISaturnSQLBuilder builder = SaturnSQLBuilderFactory
				.getSaturnSQLBuilder(dbType);

		if (builder != null) {

			String[] conditions = condition.split(ConditionUtils.SEPARATE);
			condition = conditions[0];

			List<FieldAndValueVO> voList = null;
			if (conditions.length == 3) {
				condition = conditions[1];
				if (SaturnSQLBuilderFactory.ORACLE.equals(dbType)) {
					condition = SaturnSQLUtil.parseOracleCondition(condition,
							fullKey);
				}
				ILogicContext logicContext = parameterContext.getLogicContext();
				String key = conditions[2].trim();
				voList = (List<FieldAndValueVO>) logicContext.getValue(key);

			}
			if (condition == null) {
				condition = "";
			}
			condition = " WHERE 1=1 " + condition;

			// 解析tableWord
			if (tableWord != null && !"".equals(tableWord.trim())) {
				tableWord = parseCondition(tableWord, parameterContext);
			}

			ISelectSQLBuilder selectSQLBuilder = builder
					.getSelectSQLBuilder(ISaturnSQL.SELECT_COUNT_OPERATION);
			ISaturnSQL saturnSQL = selectSQLBuilder.build2(fullKey, condition,
					tableWord);
			saturnSQL.setConditionList(voList);
			List<SaturnData> list = SaturnSQLHandler.executeQuery(saturnSQL,
					parameterContext.getConnection());
			Integer count = (Integer) list.get(0).get("count");
			return count;
		}
		return null;
	}
	
	@SuppressWarnings("unchecked")
	public static Integer deleteByCondition(String condition,IParameterContext parameterContext) throws Exception{
		String fullKey = (String) parameterContext.getParameterValue(0);

		String dbType = SaturnDataUtil.getStandardDBType(fullKey);

		ISaturnSQLBuilder builder = SaturnSQLBuilderFactory
				.getSaturnSQLBuilder(dbType);

		if (builder != null) {

			List<FieldAndValueVO> voList = null;
			String[] conditions = condition.split(ConditionUtils.SEPARATE);
			condition = conditions[0];
			if (conditions.length == 3) {
				condition = conditions[1];
				if (SaturnSQLBuilderFactory.ORACLE.equals(dbType)) {
					condition = SaturnSQLUtil.parseOracleCondition(condition,
							fullKey);
				}
				ILogicContext logicContext = parameterContext.getLogicContext();
				String key = conditions[2].trim();
				voList = (List<FieldAndValueVO>) logicContext.getValue(key);
			}
			if (condition == null) {
				condition = "";
			}
			condition = " WHERE 1=1 " + condition;
			IDeleteSQLBuilder sqlBuilder = builder
					.getDeleteSQLBuilder(ISaturnSQL.DELETE_OPERATION);
			ISaturnSQL saturnSQL = sqlBuilder.build(fullKey, condition);
			saturnSQL.setConditionList(voList);
			int count = SaturnSQLHandler.executeUpdate(saturnSQL,
					parameterContext.getConnection());
			return count;
		}
		return null;
	}
	
	@SuppressWarnings("unchecked")
	public static Integer updateByCondition(String condition,IParameterContext parameterContext) throws Exception{
		SaturnData saturnData = (SaturnData) parameterContext.getParameterValue(0);

		String dbType = SaturnDataUtil.getStandardDBType(saturnData);

		ISaturnSQLBuilder builder = SaturnSQLBuilderFactory
				.getSaturnSQLBuilder(dbType);

		if (builder != null) {

			List<FieldAndValueVO> voList = null;

			String[] conditions = condition.split(ConditionUtils.SEPARATE);

			condition = conditions[0];

			if (conditions.length == 3) {
				condition = conditions[1];
				if (SaturnSQLBuilderFactory.ORACLE.equals(dbType)) {
					condition = SaturnSQLUtil.parseOracleCondition(condition,
							saturnData);
				}
				ILogicContext logicContext = parameterContext.getLogicContext();
				String key = conditions[2].trim();
				voList = (List<FieldAndValueVO>) logicContext.getValue(key);

			}
			if (condition == null) {
				condition = "";
			}
			condition = " WHERE 1=1 " + condition;

			String specalColumn = (String) parameterContext
					.getParameterValue(3);

			IUpdateSQLBuilder sqlBuilder = builder
					.getUpdateSQLBuilder(getBuilderType(saturnData));

			ISaturnSQL saturnSQL = sqlBuilder.build(saturnData, condition,
					specalColumn);

			saturnSQL.setConditionList(voList);

			Integer count = SaturnSQLHandler.executeUpdate(saturnSQL,
					parameterContext.getConnection());
			return count;
		}
		return null;
	}
	
	private static String getBuilderType(String fullKey) {
		// 如果有大数据
		if (SaturnDataUtil.hasLOB(fullKey)) {
			return ISaturnSQL.SELECT_LOB_OPERATION;
		} else {
			return ISaturnSQL.SELECT_OPERATION;
		}
	}
	
	private static String getBuilderType(SaturnData saturnData) {
		// 如果有大数据
		if (SaturnDataUtil.hasLOB(saturnData)) {
			return ISaturnSQL.UPDATE_LOB_OPERATION;
		} else {
			return ISaturnSQL.UPDATE_OPERATION;
		}
	}
	
	private static String parseCondition(String condition,
			IParameterContext parameterContext) {
		String result = condition;
		int fromIndex = 0;
		while (result.indexOf("${", fromIndex) > -1) {
			int indexOfValueLeft = result.indexOf("${", fromIndex);
			int indexOfValueRight = result.indexOf("}", fromIndex + 1);
			String key = result.substring(indexOfValueLeft + 2,
					indexOfValueRight);
			String value = (String) parameterContext.getLogicContextValue(key
					.trim());
			if (value != null && !"".equals(value.trim())) {
				result = result.replace("${" + key + "}", value);
				fromIndex = indexOfValueLeft + value.length();
			} else {
				result = result.replace("${" + key + "}", "");
			}
		}
		return result;
	}
}
